﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using Dapper;
using Common;
using Models;
using System.Xml.Linq;

namespace DbControl
{
    public class OprUI
    {
        #region  首页
        public static DataTable GetDeevicesForUI()
        {
            using (IDbConnection connection = new SqlConnection(OprSql.connectString))
            {
                string sql = @"SELECT A.id
                          ,A.parentid 
	                      ,A.sensor_id 
                          ,A.parameter_id 
                          ,A.warn_id 
                          ,A.warn_ids 
                          ,A.device_code 
                          ,A.device_name 
                          ,A.device_position
						  ,A.install_time
                          ,B.sensor_code
                          ,B.sensor_name
                          ,B.sensor_sn
                          ,B.sensor_topic
                          ,A.oil_id     
                          ,C.oil_code
                          ,C.oil_name
					      ,C.oil_fill_time
                          ,A.flag 
                          ,B.online_status
                      FROM  devices A  left join
                         sensors B on A.sensor_id =B.id left join 
                         oils C on   A.oil_id=C.id  where A.parentid>0 AND A.flag>0 AND B.flag>0";

                DataTable dt = new DataTable();
                dt.Load(connection.ExecuteReader(sql));
                return dt;
            }
        }
        /// <summary>
        /// 设备统计
        /// </summary>
        /// <returns></returns>
        public static DataTable GetDevicesNumber()
        {
            using (IDbConnection connection = new SqlConnection(OprSql.connectString))
            {
                string sql = @"select count(id) AS 数量,  device_mode as 名称 from devices where device_mode is not null and flag>0 and parentid>0 group by device_mode";
                DataTable dt = new DataTable();
                dt.Load(connection.ExecuteReader(sql));
                return dt;
            }
        }
        public static int GetDeviceCount()
        {
            using (IDbConnection connection = new SqlConnection(OprSql.connectString))
            {
                string sql = @"select count(id) from devices where parentid>0 and flag>0 ";
                return  (int)connection.ExecuteScalar(sql);
            }
        }
        /// <summary>
        /// 终端统计
        /// </summary>
        /// <returns></returns>
        public static DataTable GetSensorsNumber()
        {
            using (IDbConnection connection = new SqlConnection(OprSql.connectString))
            {
                string sql = @"select count(Id) AS 数量,  sensor_type as 名称 from sensors where sensor_type is not null and flag>0 group by sensor_type";
                DataTable dt = new DataTable();
                dt.Load(connection.ExecuteReader(sql));
                return dt;
            }
        }
        /// <summary>
        /// 油品统计
        /// </summary>
        /// <returns></returns>
        public static DataTable GetOilsNumber()
        {
            using (IDbConnection connection = new SqlConnection(OprSql.connectString))
            {
                string sql = @"SELECT count(id) AS 数量,  oil_name as 名称 
                                FROM  (SELECT A.id,C.oil_name FROM  devices A  left join oils C on   A.oil_id=C.id  where A.parentid>0 AND A.flag>0 AND C.flag>0) B
                                group by oil_name";
                DataTable dt = new DataTable();
                dt.Load(connection.ExecuteReader(sql));
                return dt;
            }
        }
        /// <summary>
        /// 首页最近10条数据
        /// </summary>
        /// <returns></returns>
        public static DataTable GeLasterDatas()
        {
            using (IDbConnection connection = new SqlConnection(OprSql.connectString))
            {
                string sql = @"SELECT top 12 id 
                              ,create_time 
                              ,sensor_sn 
                              ,device_code 
                              ,device_name                             
                              ,temper 
                              ,dens 
                              ,vis 
                              ,vis40 
                              ,diel 
                              ,ppm      
                              ,fe1 
                              ,fe2 
                              ,fe3 
                              ,fe4 
                              ,fe5 
                              ,fe6 
                              ,fe7 
                              ,fe8 
                              ,fe9 
                              ,fe10 
                              ,nfe1 
                              ,nfe2 
                              ,nfe3 
                              ,nfe4     
                          FROM  sensordatas order by id desc";
                DataTable dt = new DataTable();
                dt.Load(connection.ExecuteReader(sql));
                return dt;
            }
        }
        public static DataTable GeLasterAlarms()
        {
            using (IDbConnection connection = new SqlConnection(OprSql.connectString))
            {
                string sql = @"SELECT top 12 id  
                              ,warn_time 
                              ,sensor_sn 
                              ,device_code 
                              ,device_name 
                              ,parameter_code
                              ,statu 
                              ,flag 
                              ,vis 
                              ,vis40 
                              ,temper 
                              ,diel 
                              ,ppm 
                              ,aw 
                              ,mo 
                              ,fe 
                              ,nfe 
                              ,iso 
                              ,dens 
                          FROM warningdatas order by id desc";
                DataTable dt = new DataTable();
                dt.Load(connection.ExecuteReader(sql));
                return dt;
            }
        }
        #endregion
        #region 数据报表查询页面
        /// <summary>
        /// 查询历史数据
        /// </summary>
        /// <returns></returns>
        public static DataTable GetSensorDatasByDeviceName(DateTime d1, DateTime d2, string Name)
        {
            using (IDbConnection connection = new SqlConnection(OprSql.connectString))
            {
                string sql = @"SELECT  id 
                              ,create_time 
                              ,sensor_sn 
                              ,device_code 
                              ,device_name                             
                              ,temper 
                              ,dens 
                              ,vis 
                              ,vis40 
                              ,diel 
                              ,ppm
                              ,aw
                              ,fe1 
                              ,fe2 
                              ,fe3 
                              ,fe4 
                              ,fe5 
                              ,fe6 
                              ,fe7 
                              ,fe8 
                              ,fe9 
                              ,fe10 
                              ,nfe1 
                              ,nfe2 
                              ,nfe3 
                              ,nfe4  
                              ,class6 
                              ,class_total
                          FROM  sensordatas WHERE create_time between @t1 and @t2 order by Id asc";
                if (!string.IsNullOrEmpty(Name))
                {
                    sql = @"SELECT  id 
                              ,create_time 
                              ,sensor_sn 
                              ,device_code 
                              ,device_name                             
                              ,temper 
                              ,dens 
                              ,vis 
                              ,vis40 
                              ,diel 
                              ,ppm
                              ,aw
                              ,fe1 
                              ,fe2 
                              ,fe3 
                              ,fe4 
                              ,fe5 
                              ,fe6 
                              ,fe7 
                              ,fe8 
                              ,fe9 
                              ,fe10 
                              ,nfe1 
                              ,nfe2 
                              ,nfe3 
                              ,nfe4  
                              ,class6 
                              ,class_total
                          FROM  sensordatas WHERE create_time between @t1 and @t2  and device_name=@DeviceName order by id asc";
                }
                DataTable dt = new DataTable();
                dt.Load(connection.ExecuteReader(sql, new { t1 = d1, t2 = d2, DeviceName = Name }));
                return dt;
            }
        }
        #endregion
        #region 曲线查询页面
        public static DataTable GetSensorDatasByDeviceNameAndPara(DateTime d1, DateTime d2, string Name,string para1)
        {
            using (IDbConnection connection = new SqlConnection(OprSql.connectString))
            {
                string sql = @"SELECT  id 
                              ,create_time 
                              ,{0}    
                          FROM  sensordatas WHERE create_time between @t1 and @t2  order by id asc";     
                sql=string.Format(sql,para1);
                if (!string.IsNullOrEmpty(Name))
                {
                    sql = @"SELECT  id 
                              ,create_time 
                              ,{0}    
                          FROM  sensordatas WHERE create_time between @t1 and @t2  and device_name=@DeviceName order by id asc";
                    sql = string.Format(sql, para1);
                }
                DataTable dt = new DataTable();
                dt.Load(connection.ExecuteReader(sql, new { t1 = d1, t2 = d2, DeviceName = Name }));
                return dt;
            }

        }
        public static DataTable GetSensorDatasByDeviceCodeAndPara(DateTime d1, DateTime d2, string deviceCode, string para1, string para2)
        {

            using (IDbConnection connection = new SqlConnection(OprSql.connectString))
            {
                string sql = @"SELECT  id 
                              ,create_time 
                              ,{0} 
                              ,{1}    
                          FROM  sensordatas WHERE create_time between @t1 and @t2 and device_code=@device_code order by id asc";     
                sql=string.Format(sql,para1 , para2);
                DataTable dt = new DataTable();
                dt.Load(connection.ExecuteReader(sql, new { t1 = d1, t2 = d2, device_code = deviceCode }));
                return dt;
            }
        }
        #endregion
        #region 设备概览页面
        public static DataTable GetDeevicesView(int parentId, bool isParent)
        {
            using (IDbConnection connection = new SqlConnection(OprSql.connectString))
            {
                string sql = "";
                if(isParent)
                    sql= @"SELECT A.id
                          ,A.parentid 
	                      ,A.sensor_id 
                          ,A.parameter_id 
                          ,A.warn_id 
                          ,A.device_code 
                          ,A.device_name 
                          ,A.device_position
                          ,A.install_time
                          ,B.sensor_code
                          ,B.sensor_name
                          ,B.sensor_sn
                          ,B.sensor_topic
                          ,A.oil_id     
                          ,C.oil_code
                          ,C.oil_name
                          ,A.flag 
                          ,B.online_status
                      FROM  devices A  left join
                         sensors B on A.sensor_id =B.id left join 
                         oils C on   A.oil_id=C.id  where A.parentid>0 AND A.flag>0  And A.id In (SELECT id FROM devices WHERE parentid=@parentid)";
                else
                    sql= @"SELECT A.id
                          ,A.parentid 
	                      ,A.sensor_id 
                          ,A.parameter_id 
                          ,A.warn_id 
                          ,A.device_code 
                          ,A.device_name 
                          ,A.device_position
                          ,A.install_time
                          ,B.sensor_code
                          ,B.sensor_name
                          ,B.sensor_sn
                          ,B.sensor_topic
                          ,A.oil_id     
                          ,C.oil_code
                          ,C.oil_name
                          ,A.flag 
                          ,B.online_status
                      FROM  devices A  left join
                         sensors B on A.sensor_id =B.id left join 
                         oils C on   A.oil_id=C.id  Where A.id=@parentid AND A.flag>0 ";
                DataTable dt = new DataTable();
                dt.Load(connection.ExecuteReader(sql, new { parentid = parentId }));
                return dt;
            }
        }
        public static DataTable GetDeevicesView(string Name)
        {
            using (IDbConnection connection = new SqlConnection(OprSql.connectString))
            {
                string sql = "";
                if (string.IsNullOrEmpty(Name))
                {
                    sql = @"SELECT A.id
                          ,A.parentid 
	                      ,A.sensor_id 
                          ,A.parameter_id 
                          ,A.warn_id 
                          ,A.device_code 
                          ,A.device_name 
                          ,A.device_position
                          ,A.install_time
                          ,B.sensor_code
                          ,B.sensor_name
                          ,B.sensor_sn
                          ,B.sensor_topic
                          ,A.oil_id     
                          ,C.oil_code
                          ,C.oil_name
                          ,A.flag 
                          ,B.online_status
                      FROM  devices A  left join
                         sensors B on A.sensor_id =B.id left join 
                         oils C on   A.oil_id=C.id where A.flag>0 and A.parentid>0";
                }
                else
                {
                    sql = string.Format(@"SELECT A.id
                          ,A.parentid 
	                      ,A.sensor_id 
                          ,A.parameter_id 
                          ,A.warn_id 
                          ,A.device_code 
                          ,A.device_name 
                          ,A.device_position
                          ,A.install_time
                          ,B.sensor_code
                          ,B.sensor_name
                          ,B.sensor_sn
                          ,B.sensor_topic
                          ,A.oil_id     
                          ,C.oil_code
                          ,C.oil_name
                          ,A.flag 
                          ,B.online_status
                      FROM  devices A  left join
                         sensors B on A.sensor_id =B.id left join 
                         oils C on   A.oil_id=C.id where A.flag>0 and A.parentid>0 and A.device_name like '%{0}%'", Name);
                }
                DataTable dt = new DataTable();
                dt.Load(connection.ExecuteReader(sql));
                return dt;
            }
        }
        #endregion
        #region 监测界面
        public static sensordatas GetMontoringInfo(string device_code)
        {
            using (IDbConnection connection = new SqlConnection(OprSql.connectString))
            {
                string sql = @"SELECT top 1 id 
                          ,sensor_sn 
                          ,device_code 
                          ,device_name 
                          ,create_time 
                          ,temper 
                          ,dens 
                          ,vis 
                          ,vis40 
                          ,diel 
                          ,ppm 
                          ,aw 
                          ,mo 
                          ,par1 
                          ,par2 
                          ,par3 
                          ,par4 
                          ,par5 
                          ,par6 
                          ,par7 
                          ,par8 
                          ,class1 
                          ,class2 
                          ,class3 
                          ,class4 
                          ,class5 
                          ,class6 
                          ,class_total 
                          ,[standard] 
                          ,fe1 
                          ,fe2 
                          ,fe3 
                          ,fe4 
                          ,fe5 
                          ,fe6 
                          ,fe7 
                          ,fe8 
                          ,fe9 
                          ,fe10 
                          ,nfe1 
                          ,nfe2 
                          ,nfe3 
                          ,nfe4 
                          ,nfe5 
                          ,fe 
                          ,nfe 
                          ,[state] 
                          ,press 
                          ,speed 
                          ,buffer_temper 
                      FROM sensordatas   Where device_code=@device_code order by Id desc ";
                return connection.Query<sensordatas>(sql, new { device_code = device_code }).SingleOrDefault();
            }
        }
        public static DataTable GetLastByTopInfo(string device_code)
        {
            using (IDbConnection connection = new SqlConnection(OprSql.connectString))
            {
                string sql= @"SELECT top 60  
                          create_time 
                          ,temper                           
                          ,vis 
                          ,diel 
                          ,ppm 
                          ,fe1 
                          ,fe2 
                          ,fe3 
                          ,fe4 
                          ,fe5 
                          ,fe6 
                          ,fe7 
                          ,fe8 
                          ,fe9 
                          ,fe10 
                          ,nfe1 
                          ,nfe2 
                          ,nfe3 
                          ,nfe4 
                          ,nfe5 
                          ,dens 
                          ,aw 
                          ,mo 
                          ,par1 
                          ,par2 
                          ,par3 
                          ,par4 
                          ,par5 
                          ,par6 
                          ,par7 
                          ,par8 
                          ,class1 
                          ,class2 
                          ,class3 
                          ,class4 
                          ,class5 
                          ,class6 
                          ,class_total 
                          ,[standard]                          
                          ,fe 
                          ,nfe 
                          ,[state] 
                          ,press 
                          ,speed 
                          ,buffer_temper 
                      FROM sensordatas   Where create_time  between '{0}' and '{1}' AND device_code=@device_code order by Id desc ";
                if(!string.IsNullOrEmpty(device_code))
                {
                    sql = @"SELECT top 60  
                          create_time 
                          ,temper                           
                          ,vis 
                          ,diel 
                          ,ppm 
                          ,fe1 
                          ,fe2 
                          ,fe3 
                          ,fe4 
                          ,fe5 
                          ,fe6 
                          ,fe7 
                          ,fe8 
                          ,fe9 
                          ,fe10 
                          ,nfe1 
                          ,nfe2 
                          ,nfe3 
                          ,nfe4 
                          ,nfe5 
                          ,dens 
                          ,aw 
                          ,mo 
                          ,par1 
                          ,par2 
                          ,par3 
                          ,par4 
                          ,par5 
                          ,par6 
                          ,par7 
                          ,par8 
                          ,class1 
                          ,class2 
                          ,class3 
                          ,class4 
                          ,class5 
                          ,class6 
                          ,class_total 
                          ,[standard]                          
                          ,fe 
                          ,nfe 
                          ,[state] 
                          ,press 
                          ,speed 
                          ,buffer_temper 
                      FROM sensordatas   Where  create_time  between '{0}' and '{1}' AND device_code=@device_code order by Id desc ";
                }
                sql = string.Format(sql, DateTime.Now.ToString("yyyy-MM-dd 00:00:00"), DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
                DataTable dt = new DataTable();
                dt.Load(connection.ExecuteReader(sql, new {  device_code = device_code }));
                return dt;
            }
        }
        #endregion
    }
}
